1. Field of the Invention.
This invention relates in general to programming development environments, and in particular, to the use of a stored procedure to access index configuration data in a remote relational database management system.
2. Description of Related Art
Computer systems incorporating Relational DataBase Management Systems (RDBMS) using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for an RDBMS and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
Indexes are often used to enhance the performance of an RDBMS in the execution of SQL statements. For each table referenced in an SQL statement, if no index exists on the table, then a table scan must be performed on that table. On the other hand, if an index is available, it will be selected for use if the RDBMS optimizer function estimates that an index scan will be faster than a table scan.
Index files generally are smaller and require less time to scan than an entire table. In addition, the entire index may not need to be scanned. The predicates applied to the index reduce the number of rows to be read from the table.
Generally, a user cannot control whether an index is used by the RDBMS in the execution of SQL statements. The RDBMS optimizer makes these decision based on available table and index information.
However, the user can play an important role in the process by creating, modifying, or deleting a configuration of indexes associated with the target tables of a query, which will affect the actions taken by the RDBMS optimizer. Thus, the configuration of indexes should be carefully chosen to address the needs of an application.
To determine whether an index should be used in a specific set of SQL statements, RDBMS vendors provide a number of different utilities. Utilities are known to exist that will recommend to users what indexes they should create for optimal performance in the RDBMS. Generally, such utilities will even create the indexes, if so instructed by the user.
A problem arises, however, when the utilities are executed by an RDBMS that is not the RDBMS for which recommendations are desired. Generally, the utilities cannot make recommendations for remote databases, because the utilities do not have access to the information necessary for making such recommendations. Thus, there is a need in the art for a mechanism that can supply the necessary information concerning remote databases to a utility that advises users concerning the optimal configuration of indexes.